package com.example.sql;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.example.sql.handle.ResultSetHandle;
import com.example.sql.handle.impl.RunoobTblHandle;
import com.example.sql.handle.impl.UserHandle;
import com.example.sql.model.RunoobTbl;
import com.example.sql.model.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MysqlDemo {
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";
    private static final String URL = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    {
        try {
            Class.forName(JDBC_DRIVER);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void query() {
        try {
            Connection connection = getConnection();
            String sql = "select * from users where username=? or id=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setObject(1, "test2");
            statement.setObject(2, "2");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String id = resultSet.getString("id");
                String name = resultSet.getString("username");
                String email = resultSet.getString("email");
                String birthdate = resultSet.getString("birthdate");
                System.out.println(id + name + email + birthdate);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void add(){
        try {
            Connection connection = getConnection();
            String sql = "insert into users(username,email) values(?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setObject(1, "test5");
            statement.setObject(2, "test5@qq.com");
            int result = statement.executeUpdate();
            System.out.println("运行了" + result + "条语句");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
            public static void delete(){
                try {
                    Connection connection=getConnection();
                    String sql="delete from users where username=?";
                    PreparedStatement statement=connection.prepareStatement(sql);
                    statement.setObject(1, "test5");
                    int result=statement.executeUpdate();
                    System.out.println("删除了"+result+"条语句");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } 

        public static void update(){
            try {
                Connection connection=getConnection();
                String sql="update users set username=? where id=?";
                PreparedStatement statement=connection.prepareStatement(sql);
                statement.setObject(1, "test0");
                statement.setObject(2, 2);
                int result=statement.executeUpdate();
                System.out.println("更新了"+result+"数据");
                
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


        public static int executUpdate(String sql,Object...objects) throws SQLException{
            //获取链接
            Connection connection=getConnection();
            //预处理sql
            PreparedStatement statement=connection.prepareStatement(sql);
            //处理参数值
            for(int i=0;i<objects.length;i++){
                //i+1意为参数从1开始
                statement.setObject(i+1, objects[i]);
                
            }
            //执行更新操作
            int result=statement.executeUpdate();
            return result;

        }


        public static void testUpdate()throws Exception{
            String sql="update users set birthdate= ? where id=? ";
            SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
            Date birthdate=  dateFormat.parse("2007-6-16");
           
           int result= executUpdate(sql, birthdate,2);
            System.out.println("更新了"+result+"条数据");
            
        }


        public static void testDelete() throws Exception{
            String sql="delete from users where id=?";
      int result = executUpdate(sql,2);
        System.out.println("删除了"+result+"条数据 ");
        }


        public static void testInsert() throws Exception{
            String sql = "insert into users (username,email,birthdate) values(?,?,?)";
            SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
            Date birthdate = dateFormat.parse("2007-06-16");
            int result = executUpdate(sql, "test1","test1@qq.com",birthdate);
            System.out.println("新增了"+result+"条数据");
        }


        public static List<User> executeQueryUserList(String sql,Object...objects ) throws SQLException{
            //获取链接
            Connection connection =getConnection();
            //预处理sql
            PreparedStatement statement=connection.prepareStatement(sql);
            //处理参数
            for(int i=0;i<objects.length;i++){
                statement.setObject(i+1,objects[i]);
            }
            //执行查询
            ResultSet resultSet=statement.executeQuery();
            //处理结果
            List<User> resultList=new ArrayList<>();
            while(resultSet.next()){
                String id=resultSet.getString("id");
                String username=resultSet.getString("username");
                String email=resultSet.getString("email");
                Date birthdate=resultSet.getDate("birthdate");
                User user=new User();
                user.setId(id);
                user.setUsername(username);
                user.setEmail(email);
                user.setBirthdate(birthdate);
                resultList.add(user);
            }

            return resultList;
        }
        public static <T> List<T> executeQuery(String sql,ResultSetHandle<T> resultSetHandle,Object...objects ) throws SQLException{
            //获取链接
            Connection connection =getConnection();
            //预处理sql
            PreparedStatement statement=connection.prepareStatement(sql);
            //处理参数
            for(int i=0;i<objects.length;i++){
                statement.setObject(i+1,objects[i]);
            }
            //执行查询
            ResultSet resultSet=statement.executeQuery();
            //处理结果
            List<T> resultList=new ArrayList<>();
            while(resultSet.next()){
               T handleResult=resultSetHandle.handle(resultSet);
               resultList.add(handleResult);
            }
            return resultList;
        }

    public static void main(String[] args) throws Exception{
      //准备sql
      String sql="select * from users where id=?";
      UserHandle handle=new UserHandle();
      List<User> list = executeQuery(sql,handle,8);
        System.out.println(list);

        String sql1="select * from runoob_tbl where runoob_author=?";
      RunoobTblHandle handleTbl=new RunoobTblHandle();
      List<RunoobTbl> listTbl = executeQuery(sql1,handleTbl,"菜鸟教程");
        System.out.println(listTbl);

    }

}
